﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Reflection;
using System.Data;
using log4net.Config;
using log4net;

namespace it.mintlab.mobilenet.mintar
{
    public class SqlServerModel : Model
    {
        protected static String connStr;
        private static readonly ILog log = LogManager.GetLogger(typeof(SqlServerModel));

        #region Insert Methods

        /// <summary>
        /// Insert model in DB without model changes
        /// </summary>
        /// <returns>true if insertion done</returns>
        public override bool insert()
        {
            return this.insertModel(this.GetType(), null);
        }

        /// <summary>
        /// Insert model in DB without model changes managing transaction
        /// </summary>
        /// <param name="transaction">transaction</param>
        /// <returns></returns>
        public bool insert(SqlTransaction transaction)
        {
            return this.insertModel(this.GetType(), transaction);
        }

        /// <summary>
        /// Insert model in DB without model changes 
        /// specifying annotation class (for ISA) managing transaction
        /// </summary>
        /// <param name="modelType">annotation class</param>
        /// <param name="transaction">transaction</param>
        /// <returns>true if insertion done</returns>
        protected bool insert(Type modelType, SqlTransaction transaction)
        {
            return this.insertModel(modelType, transaction);
        }

        /// <summary>
        /// Insert model in DB without model changes specifying annotation class (for ISA) 
        /// </summary>
        /// <param name="modelType">annotation class</param>
        /// <returns>true if insertion done</returns>
        protected bool insert(Type modelType)
        {
            return this.insertModel(modelType, null);
        }

        /// <summary>
        /// PRIVATE - Insert model in DB without model changes 
        /// specifying annotation class (for ISA) managing transaction
        /// </summary>
        /// <param name="modelType">annotation class</param>
        /// <param name="transaction">transaction</param>
        /// <returns>true if insertion done</returns>
        private bool insertModel(Type modelType, SqlTransaction transaction)
        {
            SqlConnection conn = null;

            string fields = "(";
            string values = "(";
            PropertyInfo[] properties = modelType.GetProperties(BindingFlags.DeclaredOnly | BindingFlags.Public | BindingFlags.Instance);   // for inheritance
            foreach (PropertyInfo pi in properties)
            {
                //if any attribute is present
                ModelAttr[] attrs = (ModelAttr[])pi.GetCustomAttributes(typeof(ModelAttr), false);
                if (attrs.Length == 1)
                {
                    if (attrs[0].insertable && !attrs[0].identity)
                    {
                        object obj = pi.GetValue(this, null);
                        if (obj != null)
                        {
                            if (obj.GetType().Equals(typeof(bool)))
                            {
                                fields += "[" + attrs[0].dbField + "], ";
                                values += (((bool)obj) ? "'1'" : "'0'") + ", ";
                            }
                            else if (obj.GetType().Equals(typeof(DateTime)))
                            {
                                if (((DateTime)obj).Ticks != 0)
                                {
                                    fields += "[" + attrs[0].dbField + "], ";
                                    values += "'" + String.Format("{0:s}", (DateTime)obj) + "', ";
                                }
                            }
                            else if (obj.GetType().Equals(typeof(int)) || obj.GetType().Equals(typeof(decimal)))
                            {
                                fields += "[" + attrs[0].dbField + "], ";
                                values += obj.ToString().Replace(",", ".") + ", ";
                            }
                            else
                            {
                                fields += "[" + attrs[0].dbField + "], ";
                                values += "'" + obj.ToString().Replace("'", "''") + "', ";//avoid sql injection
                            }
                        }
                    }
                }
            }
            //remove comma and finish SQL command
            fields = fields.Substring(0, fields.Length - 2) + ") ";
            values = values.Substring(0, values.Length - 2) + ") ";

            //execute query
            try
            {
                string tableName = ((ModelAttr)modelType.GetCustomAttributes(typeof(ModelAttr), true)[0]).dbTable;
                string cmdStr = "INSERT INTO [" + tableName + "] " + fields + "VALUES " + values;

                SqlCommand cmd;
                if (transaction == null)
                {
                    conn = new SqlConnection(connStr);
                    conn.Open();
                    cmd = new SqlCommand(cmdStr, conn);
                }
                else
                    cmd = new SqlCommand(cmdStr, (SqlConnection)transaction.Connection, transaction);

                //if inserted return true
                log.Info("MintAR-SQL: " + cmd.CommandText);
                if (cmd.ExecuteNonQuery() > 0)
                    return true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                try
                {
                    if (conn != null) conn.Close();
                }
                catch { }
            }
            return false;
        }

        /// <summary>
        /// Insert model in DB and change his identity value
        /// </summary>
        public override void insertIdentity()
        {
            this.insertModelIdentity(this.GetType(), null);
        }

        /// <summary>
        /// Insert model in DB and change his identity value specifying annotation class (for ISA)
        /// </summary>
        /// <param name="modelType">annotation class</param>
        protected void insertIdentity(Type modelType)
        {
            this.insertModelIdentity(modelType, null);
        }

        /// <summary>
        /// Insert model in DB and change his identity value managing transaction
        /// </summary>
        /// <param name="transaction">transaction</param>
        public void insertIdentity(SqlTransaction transaction)
        {
            this.insertModelIdentity(this.GetType(), transaction);
        }

        /// <summary>
        /// Insert model in DB and change his identity value specifying annotation class (for ISA)
        /// managing transaction
        /// </summary>
        /// <param name="transaction">transaction</param>
        /// <param name="modelType">annotation class</param>
        protected void insertIdentity(Type modelType, SqlTransaction transaction)
        {
            this.insertModelIdentity(modelType, transaction);
        }

        /// <summary>
        /// PRIVATE - Insert model in DB and change his identity value 
        /// specifying annotation class (for ISA) managing transaction
        /// </summary>
        /// <param name="modelType">annotation class</param>
        /// <param name="transaction">transaction</param>
        private void insertModelIdentity(Type modelType, SqlTransaction transaction)
        {
            SqlConnection conn = null;

            string fields = "(";
            string values = "(";
            string identityColumnName = String.Empty;
            PropertyInfo[] properties = modelType.GetProperties(BindingFlags.DeclaredOnly | BindingFlags.Public | BindingFlags.Instance);
            foreach (PropertyInfo pi in properties)
            {
                //if any attribute is present
                ModelAttr[] attrs = (ModelAttr[])pi.GetCustomAttributes(typeof(ModelAttr), true);
                if (attrs.Length == 1)
                {
                    if (attrs[0].insertable && !attrs[0].identity)
                    {
                        object obj = pi.GetValue(this, null);
                        if (obj != null)
                        {
                            if (obj.GetType().Equals(typeof(bool)))
                            {
                                fields += "[" + attrs[0].dbField + "], ";
                                values += (((bool)obj) ? "'1'" : "'0'") + ", ";
                            }
                            else if (obj.GetType().Equals(typeof(DateTime)))
                            {
                                if (((DateTime)obj).Ticks != 0)
                                {
                                    fields += "[" + attrs[0].dbField + "], ";
                                    values += "'" + String.Format("{0:s}", (DateTime)obj) + "', ";
                                }
                            }
                            else if (obj.GetType().Equals(typeof(int)) || obj.GetType().Equals(typeof(decimal)))
                            {
                                fields += "[" + attrs[0].dbField + "], ";
                                values += obj.ToString().Replace(",", ".") + ", ";
                            }
                            else
                            {
                                fields += "[" + attrs[0].dbField + "], ";
                                values += "'" + obj.ToString().Replace("'", "''") + "', ";//avoid sql injection
                            }
                        }
                    }
                    else
                        if (attrs[0].identity)
                            identityColumnName = attrs[0].dbField;
                }
            }

            //remove comma and finish SQL command
            fields = fields.Substring(0, fields.Length - 2) + ") ";
            values = values.Substring(0, values.Length - 2) + ") ";

            //execute query
            try
            {
                string tableName = ((ModelAttr)modelType.GetCustomAttributes(typeof(ModelAttr), false)[0]).dbTable;
                string cmdStr = "INSERT INTO [" + tableName + "] " + fields + "VALUES " + values;

                SqlCommand cmd, identityCmd;
                if (transaction == null)
                {
                    conn = new SqlConnection(connStr);
                    conn.Open();
                    cmd = new SqlCommand(cmdStr, conn);
                }
                else
                    cmd = new SqlCommand(cmdStr, (SqlConnection)transaction.Connection, transaction);

                SqlTransaction identityTransaction = conn.BeginTransaction();
                try
                {
                    cmd.Transaction = identityTransaction;

                    // Execute Command
                    log.Info("MintAR-SQL: " + cmd.CommandText);
                    cmd.ExecuteNonQuery();

                    if (!identityColumnName.Equals(String.Empty))
                    {
                        identityCmd = new SqlCommand("SELECT autoinc_next from information_schema.columns where table_name = '" + tableName + "' and column_name = '" + identityColumnName + "'", conn);
                        identityCmd.Transaction = identityTransaction;
                        int identity = (int)(long)identityCmd.ExecuteScalar();
                        foreach (PropertyInfo pi in properties)
                        {
                            // if any attribute is present
                            ModelAttr[] attrs = (ModelAttr[])pi.GetCustomAttributes(typeof(ModelAttr), true);
                            if (attrs.Length == 1)
                            {
                                if (attrs[0].identity)
                                {
                                    pi.SetValue(this, identity, null);
                                    break;
                                }
                            }
                        }
                    }

                    identityTransaction.Commit();
                }
                catch (SqlException sce)    // Transaction Rollback
                {
                    identityTransaction.Rollback();
                    throw sce;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                try
                {
                    if (conn != null) conn.Close();
                }
                catch { }
            }
        }

        #endregion

        #region Update Methods

        /// <summary>
        /// Update model in DB by Keys
        /// </summary>
        public override bool updateByKey()
        {
            return this.updateModelByKey(this.GetType(), null);
        }

        /// <summary>
        /// Update model in DB by Keys specifying annotation class (for ISA)
        /// </summary>
        /// <param name="modelType">annotation class</param>
        protected bool updateByKey(Type modelType)
        {
            return this.updateModelByKey(modelType, null);
        }

        /// <summary>
        /// Update model in DB by Keys managing transaction
        /// </summary>
        /// <param name="transaction">transaction</param>
        public bool updateByKey(SqlTransaction transaction)
        {
            return this.updateModelByKey(this.GetType(), transaction);
        }

        /// <summary>
        /// Update model in DB by Keys
        /// specifying annotation class (for ISA) managing transaction
        /// </summary>
        /// <param name="transaction">transaction</param>
        /// <param name="modelType">annotation class</param>
        protected bool updateByKey(Type modelType, SqlTransaction transaction)
        {
            return this.updateModelByKey(modelType, transaction);
        }

        /// <summary>
        /// PRIVATE Update model in DB by Keys
        /// specifying annotation class (for ISA) managing transaction
        /// </summary>
        /// <param name="modelType">annotation class</param>
        /// <param name="transaction">transaction</param>
        private bool updateModelByKey(Type modelType, SqlTransaction transaction)
        {
            SqlConnection conn = new SqlConnection(connStr);

            string tableName = ((ModelAttr)modelType.GetCustomAttributes(typeof(ModelAttr), false)[0]).dbTable;
            string cmdStr = "UPDATE [" + tableName + "] SET ";
            string whereStr = "WHERE ";

            PropertyInfo[] properties = modelType.GetProperties(BindingFlags.DeclaredOnly | BindingFlags.Public | BindingFlags.Instance);
            foreach (PropertyInfo pi in properties)
            {
                //if any attribute is present
                ModelAttr[] attrs = (ModelAttr[])pi.GetCustomAttributes(typeof(ModelAttr), true);
                if (attrs.Length == 1)
                {
                    if (attrs[0].key)
                    {
                        object obj = pi.GetValue(this, null);
                        if (obj != null)
                        {
                            if (obj.GetType().Equals(typeof(bool)))
                                whereStr += "[" + attrs[0].dbField + "] = " + (((bool)obj) ? "'1'" : "'0'") + " AND ";
                            else if (obj.GetType().Equals(typeof(DateTime)))
                                whereStr += "[" + attrs[0].dbField + "] = '" + String.Format("{0:s}", (DateTime)obj) + "' AND ";
                            else if (obj.GetType().Equals(typeof(int)) || obj.GetType().Equals(typeof(decimal)))
                                whereStr += "[" + attrs[0].dbField + "] = " + obj.ToString().Replace(",", ".") + " AND ";
                            else
                                whereStr += "[" + attrs[0].dbField + "] = '" + obj.ToString().Replace("'", "''") + "' AND ";//avoid sql injection
                        }
                    }
                    else
                    {
                        if (attrs[0].updatable)
                        {
                            object obj = pi.GetValue(this, null);
                            if (obj != null)
                            {
                                if (obj.GetType().Equals(typeof(bool)))
                                    cmdStr += "[" + attrs[0].dbField + "] = " + (((bool)obj) ? "'1'" : "'0'") + ", ";
                                else if (obj.GetType().Equals(typeof(DateTime)))
                                {
                                    if (((DateTime)obj).Ticks != 0)
                                        cmdStr += "[" + attrs[0].dbField + "] = '" + String.Format("{0:s}", (DateTime)obj) + "', ";
                                }
                                else if (obj.GetType().Equals(typeof(int)) || obj.GetType().Equals(typeof(decimal)))
                                    cmdStr += "[" + attrs[0].dbField + "] = " + obj.ToString().Replace(",", ".") + ", ";
                                else
                                    cmdStr += "[" + attrs[0].dbField + "] = '" + obj.ToString().Replace("'", "''") + "', ";//avoid sql injection
                            }
                        }
                    }
                }
            }
            //remove commas and finish SQL command
            cmdStr = cmdStr.Substring(0, cmdStr.Length - 2) + " " + whereStr.Substring(0, whereStr.Length - 4);

            //execute query
            try
            {
                SqlCommand cmd;
                if (transaction == null)
                {
                    conn = new SqlConnection(connStr);
                    conn.Open();
                    cmd = new SqlCommand(cmdStr, conn);
                }
                else
                    cmd = new SqlCommand(cmdStr, (SqlConnection)transaction.Connection, transaction);

                //if inserted return true
                log.Info("MintAR-SQL: " + cmd.CommandText);
                if (cmd.ExecuteNonQuery() > 0)
                    return true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                try
                {
                    if (conn != null) conn.Close();
                }
                catch { }
            }
            return false;
        }

        #endregion

        #region Delete Methods

        /// <summary>
        /// Delete model in DB by Keys
        /// </summary>
        public override bool deleteByKey()
        {
            return this.deleteModelByKey(this.GetType(), null);
        }

        /// <summary>
        /// Delete model in DB by Keys specifying annotation class (for ISA)
        /// </summary>
        /// <param name="modelType">annotation class</param>
        protected bool deleteByKey(Type modelType)
        {
            return this.deleteModelByKey(modelType, null);
        }

        /// <summary>
        /// Delete model in DB by Keys managing transaction
        /// </summary>
        /// <param name="transaction">transaction</param>
        public bool deleteByKey(SqlTransaction transaction)
        {
            return this.deleteModelByKey(this.GetType(), transaction);
        }

        /// <summary>
        /// Delete model in DB by Keys
        /// specifying annotation class (for ISA) managing transaction
        /// </summary>
        /// <param name="transaction">transaction</param>
        /// <param name="modelType">annotation class</param>
        protected bool deleteByKey(Type modelType, SqlTransaction transaction)
        {
            return this.deleteModelByKey(modelType, transaction);
        }

        /// <summary>
        /// PRIVATE Delete model in DB by Keys
        /// specifying annotation class (for ISA) managing transaction
        /// </summary>
        /// <returns></returns>
        private bool deleteModelByKey(Type modelType, SqlTransaction transaction)
        {
            SqlConnection conn = new SqlConnection(connStr);

            string tableName = ((ModelAttr)modelType.GetCustomAttributes(typeof(ModelAttr), false)[0]).dbTable;
            string cmdStr = "DELETE FROM [" + tableName + "] WHERE ";

            PropertyInfo[] properties = modelType.GetProperties(BindingFlags.DeclaredOnly | BindingFlags.Public | BindingFlags.Instance);
            foreach (PropertyInfo pi in properties)
            {
                //if any attribute is present
                ModelAttr[] attrs = (ModelAttr[])pi.GetCustomAttributes(typeof(ModelAttr), true);
                if (attrs.Length == 1)
                {
                    if (attrs[0].key)
                    {
                        object obj = pi.GetValue(this, null);
                        if (obj.GetType().Equals(typeof(bool)))
                            cmdStr += "[" + attrs[0].dbField + "] = " + (((bool)obj) ? "'1'" : "'0'") + " AND ";
                        else if (obj.GetType().Equals(typeof(DateTime)))
                            cmdStr += "[" + attrs[0].dbField + "] = '" + String.Format("{0:s}", (DateTime)obj) + "' AND ";
                        else if (obj.GetType().Equals(typeof(int)) || obj.GetType().Equals(typeof(decimal)))
                            cmdStr += "[" + attrs[0].dbField + "] = " + obj.ToString().Replace(",", ".") + " AND ";
                        else
                            cmdStr += "[" + attrs[0].dbField + "] = '" + obj.ToString().Replace("'", "''") + "' AND ";//avoid sql injection

                    }
                }
            }
            //remove commas and finish SQL command
            cmdStr = cmdStr.Substring(0, cmdStr.Length - 4);

            //execute query
            try
            {
                SqlCommand cmd;
                if (transaction == null)
                {
                    conn = new SqlConnection(connStr);
                    conn.Open();
                    cmd = new SqlCommand(cmdStr, conn);
                }
                else
                    cmd = new SqlCommand(cmdStr, (SqlConnection)transaction.Connection, transaction);

                //if inserted return true
                log.Info("MintAR-SQL: " + cmd.CommandText);
                if (cmd.ExecuteNonQuery() > 0)
                    return true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                try
                {
                    if (conn != null) conn.Close();
                }
                catch { }
            }
            return false;
        }

        #endregion

        #region Select Methods

        /// <summary>
        /// Get Model by key columns
        /// </summary>
        /// <returns>true id found</returns>
        public override bool selectByKey()
        {
            return this.selectByKey(this.GetType());
        }

        /// <summary>
        /// Get Model by key columns specifying annotation class (for ISA)
        /// </summary>
        /// <param name="modelType">annotation class</param>
        /// <returns>Model selected</returns>
        public override bool selectByKey(Type modelType)
        {
            SqlConnection conn = new SqlConnection(connStr);
            SqlDataReader reader = null;

            string tableName = ((ModelAttr)modelType.GetCustomAttributes(typeof(ModelAttr), true)[0]).dbTable;
            //string cmdStr = "SELECT TOP 1 * FROM [" + tableName + "] WHERE ";
            string cmdStr = "SELECT * FROM [" + tableName + "] WHERE ";

            try
            {

                PropertyInfo[] properties = modelType.GetProperties(BindingFlags.DeclaredOnly | BindingFlags.Public | BindingFlags.Instance);
                foreach (PropertyInfo pi in properties)
                {
                    // if any attribute is present
                    ModelAttr[] attrs = (ModelAttr[])pi.GetCustomAttributes(typeof(ModelAttr), true);
                    if (attrs.Length == 1)
                    {
                        if (attrs[0].key)
                        {
                            object obj = pi.GetValue(this, null);
                            if (obj.GetType().Equals(typeof(bool)))
                                cmdStr += "[" + attrs[0].dbField + "] = " + (((bool)obj) ? "'1'" : "'0'") + " AND ";
                            else if (obj.GetType().Equals(typeof(DateTime)))
                                cmdStr += "[" + attrs[0].dbField + "] = '" + String.Format("{0:s}", (DateTime)obj) + "' AND ";
                            else if (obj.GetType().Equals(typeof(int)) || obj.GetType().Equals(typeof(decimal)))
                                cmdStr += "[" + attrs[0].dbField + "] = " + obj.ToString().Replace(",", ".") + " AND ";
                            else
                                cmdStr += "[" + attrs[0].dbField + "] = '" + obj.ToString().Replace("'", "''") + "' AND ";//avoid sql injection

                        }
                    }
                }
                //remove AND
                cmdStr = cmdStr.Substring(0, cmdStr.Length - 4);

                //execute query
                conn.Open();
                SqlCommand cmd = new SqlCommand(cmdStr, conn);
                log.Info("MintAR-SQL: " + cmd.CommandText);
                reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    foreach (PropertyInfo pi in properties)
                    {
                        //if any attribute is present
                        Object[] attrs = pi.GetCustomAttributes(typeof(ModelAttr), true);

                        if (attrs.Length == 1)
                        {
                            string fieldName = ((ModelAttr)attrs[0]).dbField.Replace("[", "").Replace("]", "");
                            if (!(reader[fieldName] is DBNull) && ((ModelAttr)attrs[0]).selectable)
                            {
                                if (pi.PropertyType.Equals(typeof(bool)))
                                {
                                    pi.SetValue(this, reader[fieldName].ToString().Equals("1") ? true : false, null);
                                }
                                else if (pi.PropertyType.Equals(typeof(DateTime)))
                                {
                                    DateTime dt = (DateTime)reader[fieldName];
                                    pi.SetValue(this, dt, null);
                                }
                                else if (pi.PropertyType.Equals(typeof(int)) || pi.PropertyType.Equals(typeof(decimal)))
                                {
                                    pi.SetValue(this, reader[fieldName], null);
                                }
                                else
                                {
                                    pi.SetValue(this, reader[fieldName].ToString(), null);
                                }
                            }
                        }
                    }
                    return true;
                }
                return false;
            }

            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                try { reader.Close(); }
                catch { }
                try { conn.Close(); }
                catch { }
            }
        }

        /// <summary>
        /// Get List of all records
        /// </summary>
        /// <returns>records</returns>
        public override Model selectFirst()
        {
            return this.selectFirst(this.GetType());
        }

        /// <summary>
        /// Get first record
        /// </summary>
        /// <param name="modelType">annotation class</param>
        /// <returns>records</returns>
        public override Model selectFirst(Type modelType)
        {
            SqlConnection conn = new SqlConnection(connStr);
            SqlDataReader reader = null;

            string tableName = ((ModelAttr)modelType.GetCustomAttributes(typeof(ModelAttr), true)[0]).dbTable;
            string cmdStr = "SELECT * FROM [" + tableName + "]";

            try
            {
                //execute query
                conn.Open();
                SqlCommand cmd = new SqlCommand(cmdStr, conn);
                log.Info("MintAR-SQL: " + cmd.CommandText);
                reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    Model model = Model.createModel(modelType);

                    PropertyInfo[] properties = modelType.GetProperties(BindingFlags.DeclaredOnly | BindingFlags.Public | BindingFlags.Instance);
                    foreach (PropertyInfo pi in properties)
                    {
                        //if any attribute is present
                        Object[] attrs = pi.GetCustomAttributes(typeof(ModelAttr), true);
                        if (attrs.Length == 1)
                        {
                            string fieldName = ((ModelAttr)attrs[0]).dbField.Replace("[", "").Replace("]", "");
                            if (!(reader[fieldName] is DBNull) && ((ModelAttr)attrs[0]).selectable)
                            {
                                if (pi.PropertyType.Equals(typeof(bool)))
                                {
                                    pi.SetValue(model, reader[fieldName].ToString().Equals("1") ? true : false, null);
                                }
                                else if (pi.PropertyType.Equals(typeof(DateTime)))
                                {
                                    DateTime dt = (DateTime)reader[fieldName];
                                    pi.SetValue(model, dt, null);
                                }
                                else if (pi.PropertyType.Equals(typeof(int)) || pi.PropertyType.Equals(typeof(decimal)))
                                {
                                    pi.SetValue(model, reader[fieldName], null);
                                }
                                else
                                {
                                    pi.SetValue(model, reader[fieldName].ToString(), null);
                                }
                            }
                        }
                    }
                    return model;
                }
                return null;
            }

            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                try { reader.Close(); }
                catch { }
                try { conn.Close(); }
                catch { }
            }
        }

        /// <summary>
        /// Get first record by sql filter 
        /// </summary>
        /// <returns>true id found</returns>
        public override Model selectFirstByFilter(String filter)
        {
            return this.selectFirstByFilter(this.GetType(), filter);
        }

        /// <summary>
        /// Get first record by sql filter 
        /// </summary>
        /// <param name="modelType">annotation class</param>
        /// <returns>Model selected</returns>
        public override Model selectFirstByFilter(Type modelType, String filter)
        {
            SqlConnection conn = new SqlConnection(connStr);
            SqlDataReader reader = null;

            string tableName = ((ModelAttr)modelType.GetCustomAttributes(typeof(ModelAttr), true)[0]).dbTable;
            string cmdStr = "SELECT * FROM [" + tableName + "] " + filter;

            if (filter.StartsWith("WHERE "))
            {
                try
                {
                    //execute query
                    conn.Open();
                    SqlCommand cmd = new SqlCommand(cmdStr, conn);
                    log.Info("MintAR-SQL: " + cmd.CommandText);
                    reader = cmd.ExecuteReader();

                    while (reader.Read())
                    {
                        Model model = Model.createModel(modelType);

                        PropertyInfo[] properties = modelType.GetProperties(BindingFlags.DeclaredOnly | BindingFlags.Public | BindingFlags.Instance);
                        foreach (PropertyInfo pi in properties)
                        {
                            //if any attribute is present
                            Object[] attrs = pi.GetCustomAttributes(typeof(ModelAttr), true);

                            if (attrs.Length == 1)
                            {
                                string fieldName = ((ModelAttr)attrs[0]).dbField.Replace("[", "").Replace("]", "");
                                if (!(reader[fieldName] is DBNull) && ((ModelAttr)attrs[0]).selectable)
                                {
                                    if (pi.PropertyType.Equals(typeof(bool)))
                                    {
                                        pi.SetValue(model, reader[fieldName].ToString().Equals("1") ? true : false, null);
                                    }
                                    else if (pi.PropertyType.Equals(typeof(DateTime)))
                                    {
                                        DateTime dt = (DateTime)reader[fieldName];
                                        pi.SetValue(model, dt, null);
                                    }
                                    else if (pi.PropertyType.Equals(typeof(int)) || pi.PropertyType.Equals(typeof(decimal)))
                                    {
                                        pi.SetValue(model, reader[fieldName], null);
                                    }
                                    else
                                    {
                                        pi.SetValue(model, reader[fieldName].ToString(), null);
                                    }
                                }
                            }
                        }
                        return model;
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    try { reader.Close(); }
                    catch { }
                    try { conn.Close(); }
                    catch { }
                }
            }
            return null;
        }

        /// <summary>
        /// Get List of all records
        /// </summary>
        /// <returns>records</returns>
        public override List<Model> select()
        {
            return this.select(this.GetType());
        }

        /// <summary>
        /// Get List of all records
        /// </summary>
        /// <param name="modelType">annotation class</param>
        /// <returns>records</returns>
        public override List<Model> select(Type modelType)
        {
            SqlConnection conn = new SqlConnection(connStr);
            SqlDataReader reader = null;

            string tableName = ((ModelAttr)modelType.GetCustomAttributes(typeof(ModelAttr), true)[0]).dbTable;
            string cmdStr = "SELECT * FROM [" + tableName + "]";
            List<Model> result = new List<Model>();

            try
            {
                //execute query
                conn.Open();
                SqlCommand cmd = new SqlCommand(cmdStr, conn);
                log.Info("MintAR-SQL: " + cmd.CommandText);
                reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    Model model = Model.createModel(modelType);

                    PropertyInfo[] properties = modelType.GetProperties(BindingFlags.DeclaredOnly | BindingFlags.Public | BindingFlags.Instance);
                    foreach (PropertyInfo pi in properties)
                    {
                        //if any attribute is present
                        Object[] attrs = pi.GetCustomAttributes(typeof(ModelAttr), true);
                        if (attrs.Length == 1)
                        {
                            string fieldName = ((ModelAttr)attrs[0]).dbField.Replace("[", "").Replace("]", "");
                            if (!(reader[fieldName] is DBNull) && ((ModelAttr)attrs[0]).selectable)
                            {
                                if (pi.PropertyType.Equals(typeof(bool)))
                                {
                                    pi.SetValue(model, reader[fieldName].ToString().Equals("1") ? true : false, null);
                                }
                                else if (pi.PropertyType.Equals(typeof(DateTime)))
                                {
                                    DateTime dt = (DateTime)reader[fieldName];
                                    pi.SetValue(model, dt, null);
                                }
                                else if (pi.PropertyType.Equals(typeof(int)) || pi.PropertyType.Equals(typeof(decimal)))
                                {
                                    pi.SetValue(model, reader[fieldName], null);
                                }
                                else
                                {
                                    pi.SetValue(model, reader[fieldName].ToString(), null);
                                }
                            }
                        }
                    }
                    result.Add(model);
                }
                return result;
            }

            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                try { reader.Close(); }
                catch { }
                try { conn.Close(); }
                catch { }
            }
        }

        /// <summary>
        /// Get List of records by sql filter 
        /// </summary>
        public override List<Model> selectByFilter(String filter)
        {
            return this.selectByFilter(this.GetType(), filter);
        }

        /// <summary>
        /// Get List of records by sql filter 
        /// </summary>
        public override List<Model> selectByFilter(Type modelType, String filter)
        {
            SqlConnection conn = new SqlConnection(connStr);
            SqlDataReader reader = null;

            string tableName = ((ModelAttr)modelType.GetCustomAttributes(typeof(ModelAttr), true)[0]).dbTable;
            string cmdStr = "SELECT * FROM [" + tableName + "] " + filter;
            List<Model> result = new List<Model>();

            if (filter.StartsWith("WHERE "))
            {
                try
                {
                    //execute query
                    conn.Open();
                    SqlCommand cmd = new SqlCommand(cmdStr, conn);
                    log.Info("MintAR-SQL: " + cmd.CommandText);
                    reader = cmd.ExecuteReader();

                    while (reader.Read())
                    {
                        Model model = Model.createModel(modelType);

                        PropertyInfo[] properties = modelType.GetProperties(BindingFlags.DeclaredOnly | BindingFlags.Public | BindingFlags.Instance);
                        foreach (PropertyInfo pi in properties)
                        {
                            //if any attribute is present
                            Object[] attrs = pi.GetCustomAttributes(typeof(ModelAttr), true);

                            if (attrs.Length == 1)
                            {
                                string fieldName = ((ModelAttr)attrs[0]).dbField.Replace("[", "").Replace("]", "");
                                if (!(reader[fieldName] is DBNull) && ((ModelAttr)attrs[0]).selectable)
                                {
                                    if (pi.PropertyType.Equals(typeof(bool)))
                                    {
                                        pi.SetValue(model, reader[fieldName].ToString().Equals("1") ? true : false, null);
                                    }
                                    else if (pi.PropertyType.Equals(typeof(DateTime)))
                                    {
                                        DateTime dt = (DateTime)reader[fieldName];
                                        pi.SetValue(model, dt, null);
                                    }
                                    else if (pi.PropertyType.Equals(typeof(int)) || pi.PropertyType.Equals(typeof(decimal)))
                                    {
                                        pi.SetValue(model, reader[fieldName], null);
                                    }
                                    else
                                    {
                                        pi.SetValue(model, reader[fieldName].ToString(), null);
                                    }
                                }
                            }
                        }
                        result.Add(model);
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    try { reader.Close(); }
                    catch { }
                    try { conn.Close(); }
                    catch { }
                }
            }
            return result;
        }

        #endregion

        public static DataTable getMapTable(string table, bool firstEmptyRow)
        {
            SqlConnection conn = new SqlConnection(SqlServerUtils.ConnStr);
            string cmdStr = "SELECT id, value FROM [" + table + "]";
            log.Info("MintAR-SQL: " + cmdStr);
            SqlDataAdapter adapter = new SqlDataAdapter(cmdStr, conn);
            DataTable dt;
            //execute query
            try
            {
                dt = new DataTable();
                adapter.Fill(dt);
                if (firstEmptyRow)
                {
                    DataRow dr = dt.NewRow();
                    dr[0] = -1;
                    dr[1] = "";
                    dt.Rows.InsertAt(dr, 0);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return dt;
        }
    }
}
